SET NOCOUNT ON;
declare @version smallint = 1;
declare @release smallint = 3;
declare @script_name varchar(256)
set @script_name='UtilityDB_V'+convert(varchar(5),@version)+'R'
	+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=0)
	print 'script '+@script_name+' already executed, please undo then re-execute.';
else
begin try
begin tran
/*--Begin Code Here --*/

declare @tsql varchar(max);

create type  [udb].[logging_table] as table (
[date_logged] [datetime2](7) NOT NULL,
[calling_sql_name] [varchar](257) NOT NULL,
[user_name] [sysname] NOT NULL,
[step] [smallint] NOT NULL,
[total_steps] [smallint] NOT NULL,
[message] [varchar](1000) NULL
)

create table udb.logging (
logging_id bigInt identity(1,1) not null
,set_id uniqueidentifier  not null
,date_logged datetime2 not null
,calling_sql_name varchar(257) not null
,[user_name] sysname not null
,step smallint not null
,total_steps smallInt not null
,[message] varchar(1000)
,constraint pk_logging primary key (logging_id)
);

set @tsql='
create procedure udb.logInsert
@set_id uniqueidentifier=null
,@calling_sql_name varchar(1000)
,@step smallInt=1
,@total_steps smallInt=1
,@message varchar(1000)
,@user_name sysname =null
,@user_type tinyInt =1
,@raise_error bit=1
as
BEGIN
BEGIN TRY
SET NOCOUNT ON;
declare @date_logged datetime2;
declare @proc_set_id uniqueidentifier;

if @set_id is null
	set @proc_set_id=newid();
else
	set @proc_set_id=@set_id;

set @date_logged = SYSDATETIME();

if @user_name is null
	if @user_type=1 
		set @user_name=SUSER_NAME();
	if @user_type=2
		set @user_name=USER_NAME();
	if @user_type=3
		set @user_name=ORIGINAL_LOGIN(); 

INSERT INTO udb.[logging]
([set_id]
,[date_logged]
,[calling_sql_name]
,[user_name]
,[step]
,[total_steps]
,[message])
VALUES
(@proc_set_id
,@date_logged
,@calling_sql_name
,@user_name
,@step
,@total_steps
,@message)


END TRY
BEGIN CATCH
	if @raise_error=1
		BEGIN
			declare @error_message nvarchar(2048);
			set @error_message=ERROR_MESSAGE();
			RAISERROR(@error_message,16,1);	
		END
	else
	return(0);
END CATCH
END
';
execute (@tsql);


set @tsql='
create procedure udb.logInsertTableVariable 
@logging_table udb.logging_table READONLY
,@raise_error bit=1
,@set_id uniqueidentifier=null
as
BEGIN
BEGIN TRY
SET NOCOUNT ON;
declare @proc_set_id uniqueidentifier;

if @set_id is null
	set @proc_set_id=newid();
else
	set @proc_set_id=@set_id;

INSERT INTO udb.[logging]
([set_id]
,[date_logged]
,[calling_sql_name]
,[user_name]
,[step]
,[total_steps]
,[message])
select
@proc_set_id
,[date_logged]
,[calling_sql_name]
,[user_name]
,[step]
,[total_steps]
,[message]
from
@logging_table;

END TRY
BEGIN CATCH
	if @raise_error=1
		BEGIN
			declare @error_message nvarchar(2048);
			set @error_message=ERROR_MESSAGE();
			RAISERROR(@error_message,16,1);	
		END
	else
	return(0);
END CATCH
END
'

execute (@tsql);

/*--End Code Here --*/
if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	update dbo.database_version_information 
	set undone=0
	,date_released=getdate()
	where
	script_name=@script_name;
else
	insert into dbo.database_version_information 
	values(@script_name,@version,@release,GETDATE(),0);


print 'script '+@script_name+' succeeded!'
commit tran
end try
begin catch 
	declare @error_message varchar(2000);
	set @error_message='SCRIPT:'+@script_name+' FAILED! ROLLBACK!  ERROR_MESSAGE:'
	+coalesce(error_message(),'')+' ERROR_NUMBER:'+coalesce(convert(varchar(10),error_number()),'');
	ROLLBACK
	raiserror(@error_message,16,1)
end catch